# Chapter 15. SQL Reference

#### Contents:

MySQL SQL
mSQL SQL

## 15.1. MySQL SQL

ALTER/MODIFY |

    ALTER [IGNORE] TABLE table ADD [COLUMN] create_clause
    ALTER [IGNORE] TABLE table ADD INDEX [name] (column, . . .)
    ALTER [IGNORE] TABLE table ADD UNIQUE [name] (column, . . .)
    ALTER [IGNORE] TABLE table ALTER [COLUMN] column SET DEFAULT value
    ALTER [IGNORE] TABLE table ALTER [COLUMN] column DROP DEFAULT
    ALTER [IGNORE] TABLE table CHANGE [COLUMN] column create_clause
    ALTER [IGNORE] TABLE table DROP [COLUMN] column
    ALTER [IGNORE] TABLE table DROP FOREIGN KEY key
    ALTER [IGNORE] TABLE table DROP INDEX key
    ALTER [IGNORE] TABLE table DROP PRIMARY KEY
    ALTER [IGNORE] TABLE table MODIFY [COLUMN] create_clause
    ALTER [IGNORE] TABLE> table RENAME [AS] new_name

The `ALTER` statement covers a wide range of actions that modify the structure of a table. This statement is used to add, change, or remove columns from an existing table as well as to remove indexes. Multiple `ALTER` statements may be combined into one using commas as in the following example:

>     ALTER TABLE mytable DROP myoldcolumn, ADD mynewcolumn INT

To perform modifications on the table, MySQL creates a copy of the table and changes it, meanwhile queuing all table altering queries. When the change is done, the old table is removed and the new table put it its place. At this point the queued queries are performed. As a safety precaution, if any of the queued queries create duplicate keys that should be unique, the `ALTER` statement is rolled back and cancelled. If the `IGNORE` keyword is present in the statement, duplicate unique keys are ignored and the `ALTER` statement proceeds as if normal. Be warned that using `IGNORE` on an active table with unique keys is inviting table corruption.

As mentioned earlier, there are several different, often orthogonal, actions performed by `ALTER`:

`ADD [COLUMN]` create_clause

  Inserts a new column into the table. The `create_clause` is of the same type as used by the `CREATE` statement (see later). The table must already exist and must not have a column with the same name as the new one. (The `COLUMN` keyword is optional and has no effect.)

`ADD INDEX` [name] (column, ...)

  Creates an index out of the given columns. Up to 15 columns may be combined in an index. Naming an index is optional. If no name is given, the index will be named after the first column listed (with a numerical suffix `_2`, `_3`, etc., for uniqueness if necessary).

`ADD UNIQUE` [name] (column, ...)

  Is identical to `ADD INDEX` except that the values of the indexed columns are guaranteed to be unique. That is, if a user attempts to add a value that already exists to a unique index, an error will be returned.

`ALTER [COLUMN]` column `SET DEFAULT` value
`ALTER [COLUMN]` column `DROP DEFAULT`

  Creates, modifies or deletes the default value of a column. When the `SET DEFAULT` phrase is used, the default value of the column is set to the new value (even if no default previously existed). When `DROP DEFAULT` is used, any existing default value is removed. If the default is dropped, any existing rows that were created with the default value are left untouched. (The `COLUMN` keyword is optional and has no effect.)

`CHANGE [COLUMN]` new_column_name create_clause
`MODIFY [COLUMN]` create_clause

  Alters the definition of a column. This statement is used to change a column from one type to a different type while affecting the data as little as possible. The create clause is a full clause as specified in the CREATE statement. This includes the name of the column. Because of this, you change the name of the column using this statement. (For example, `ALTER TABLE mytable CHANGE name newname CHAR(30)` ). The `MODIFY` version is the same as `CHANGE` if the new column has the same name as the old. The `COLUMN` keyword is optional and has no effect. The following conversions are done automatically:

  - Integer to Floating Point, and vice versa (e.g., `BIGINT` to `DOUBLE`).

  - Smaller numerical value to larger numerical value (e.g., `INTEGER` to `BIGINT`).

  - Larger numerical value to smaller numerical value (e.g., `DOUBLE` to FLOAT). If a value is beyond the limits of the new type, the highest (or greatest negative) possible value of the new type is used instead.

  - Numerical to character (e.g., `SMALLINT` to `CHAR(5)`).

  - Character to numerical (e.g., `VARCHAR` to `MEDIUMINT`). Either integer or floating point conversion is performed upon the text (whichever is appropriate for the new type).

  - Smaller character to larger character (e.g., `BLOB` to `LONGTEXT`).

  - Larger character to smaller character (e.g., `TEXT` to `VARCHAR(255)`). If a value is longer than the limits of the new type, the text is truncated to fit the new type.

  - Even for conversions that are not mentioned here (e.g., `TIMESTAMP` to `YEAR`), MySQL will try its best to perform a reasonable conversion. Under no circumstance will MySQL give up and return an error when using this statement; a conversion of some sort will always be done. With this in mind you should (1) make a backup of the data before the conversion and (2) immediately check the new values to see if they are reasonable.

`DROP [COLUMN]` column

  Deletes a column from a table. This statement will remove a column and all of its data from a table permanently. There is no way to recover data destroyed in this manner other than from backups. All references to this column in indices will be removed. Any indices where this was the sole column will be destroyed as well. (The `COLUMN` keyword is optional and has no effect.)

`DROP INDEX` key

  Removes an index from a table. This statement will completely erase an index from a table. This statement will not delete or alter any of the table data itself, only the index data. Therefore, an index removed in this manner can be recreated using the `ALTER TABLE ... ADD INDEX` statement.

`DROP PRIMARY KEY`

  Identical to `DROP INDEX` except that it looks for the special index known as the Primary Key. If no Primary Key is found in the table, the first unique key is deleted.

`RENAME [AS]` new_table

  Changes the name of the table. This operation does not affect any of the data or indices within the table, only the table's name. If this statement is performed alone, without any other `ALTER TABLE` clauses, MySQL will not create a temporary table as with the other clauses, but simply perform a fast Unix-level rename of the table files.

The `FOREIGN KEY` operation is currently not implemented in MySQL. While the syntax is there, attempting an action on a `FOREIGN KEY` will do nothing.

To perform any of the `ALTER TABLE` actions, you must have `SELECT, INSERT, DELETE, UPDATE, CREATE,` and `DROP` privileges for the table in question.

#### Examples

>     # Add the field 'address2' to the table 'people' and make it of type 'VARCHAR'
>     # with a maximum length of 200.
>     ALTER TABLE people ADD COLUMN address2 VARCHAR(100)
>     # Add two new indexes to the 'hr' table, one regular index for the 'salary'
>     # field and one unique index for the 'id' field. Also, continue operation if
>     # duplicate values are found while creating the 'id_idx' index (very dangerous!).
>     ALTER TABLE hr ADD INDEX salary_idx ( salary )
>     ALTER IGNORE TABLE hr ADD UNIQUE id_idx ( id )
>     # Change the default value of the 'price' field in the 'sprockets' table to
>     # $19.95.
>     ALTER TABLE sprockets ALTER price SET DEFAULT '$19.95'
>     # Remove the default value of the 'middle_name' field in the 'names' table.
>     ALTER TABLE names ALTER middle_name DROP DEFAULT
>     # Change the type of the field 'profits' from its previous value (which was
>     # perhaps INTEGER) to BIGINT.
>     ALTER TABLE finanaces CHANGE COLUMN profits profits BIGINT
>     # Remove the 'secret_stuff' field from the table 'not_private_anymore'
>     ALTER TABLE not_private_anymore DROP secret_stuff
>     # Delete the named index 'id_index' as well as the primary key from the
>     # table 'cars'.
>     ALTER TABLE cars DROP INDEX id_index, DROP PRIMARY KEY
>     # Rename the table 'rates_current' to 'rates_1997'
>     ALTER TABLE rates_current RENAME AS rates_1997

CREATE |

    CREATE DATABASE dbname
    CREATE TABLE name (field_namefield_type, ... )
    CREATE INDEX name ONtable ( column, ... )

    CREATE FUNCTIONname RETURNSvalues SONAMElibrary

Creates new database elements (or entirely new databases). This statement is used to create databases, tables, and user defined functions (UDFs).

The `CREATE INDEX` statement is provided for compatibility with other implementations of SQL. In older versions of SQL this statement does nothing. As of 3.22, this statement is equivalent to the `ALTER TABLE ADD INDEX` statement. To perform the `CREATE INDEX` statement, you must have `INDEX` privileges for the table in question.

The `CREATE DATABASE` statement creates an entirely new, empty database. This is equivalent to running the `mysqladmin create` utility. As with running `mysqladmin`, you must be the administrative user for MySQL (usually `root` or `mysql`) to perform this statement.

The `CREATE FUNCTION` statement allows MySQL statements to access precompiled executable functions. These functions can perform practically any operation, since they are designed and implemented by the user. The return value of the function can be `STRING`, for character data; `REAL`, for floating point numbers; or `INTEGER` for integer numbers. MySQL will translate the return value of the C function to the indicated type. The library file that contains the function must be a standard shared library that MySQL can dynamically link into the server.

The `CREATE TABLE` statement defines the structure of a table within the database. This statement is how all MySQL tables are created. This statement consists of the name of the new table followed by any number of field definitions. The syntax of a field definition is the name of the field followed by its type, followed by any modifiers (e.g., `name char(30) not null`). MySQL supports the following datatypes, as shown in Table 15-1.

#### Table 15-1. Datatypes

Type | (in bytes) | Comments
--- | --- | ---
`TINYINT(` length`)/INT1(`length`)` | 1 | Integer with unsigned range of 0-255 and signed range of -128-127.
`SMALLINT(` length`)/INT2(`length`)` | 2 | Integer with unsigned range of 0-65535 and signed range of -32768-32767.
`MEDIUMINT(` length`)/INT3(`length`)/MIDDLEINT(` length`)` | 3 | -8388608-8388607.
`INT(` length`)/INTEGER(`length`)/INT4(`length`)` | 4 | -2147483648-2147483647.
`BIGINT(` length`)/INT8(`length`)` | 8 | 0-18446744-7370955165 and signed range of -9223372036854775808-9223372036854775807.
`FLOAT/FLOAT(4)/FLOAT(` length`,`decimal`)/FLOAT4(`length`,`decimal`)` | 4 | Floating point number with maximum value +/-3.402823466E38 and minimum (non-zero) value +/-1.175494351E-38.
`DOUBLEPRECISION(` length`,`decimal`)/DOUBLE(` length`,`decimal`)/REAL(` length`,`decimal`)/FLOAT8(`length`,`decimal`)/FLOAT(8)` | 8 | +/-2.2250738585072014E-308.
`DECIMAL(` length`,`decimal`)/NUMERIC(` length`,`decimal`)` | length | Floating point number with the range of the `DOUBLE` type that is stored as a `CHAR` field. `DECIMAL` and `NUMERIC` are always treated as `CHAR` fields that just happen to contain a numeric value.
`TIMESTAMP(` length`)` | 4 | A timestamp value that updates every time the row is modified. You may also assign a value to field manually. Assigning a `NULL``YYMMDD'.
`` `DATE` | 3 | A date value that stores the year, month and date. Values are always output with the format `YYYY-MM-DD', but may be entered in any of the following formats: `YY-MM-DD', `YYYY-MM-DD', `YYMMDD', or `YYYYMMDDHHMSS' (the time portion is ignored).
`TIME` | 3 | A time value that stores the hour, minute and second. Values are always output with the format `HH:MM:SS' but may be entered in any of the following formats: `HH:MM:SS', `HHMMSS', `HHMM' (seconds are set to 0), or `HH' (minutes and seconds are set to 0).
`` `DATETIME` | 8 | A value that stores both the date and the time. Values are both input and output as `YYYY-MM-DD HH:MM:SS'.
`YEAR` | 1 | A value that stores the year. Values can be input in either the `YYYY' or `YY' format and will be output as two- or four-digit years depending on the input format. Two digit years are assumed to lie between 1970 and 2069, inclusive. This type currently only understands years within the range of 1901 to 2155.
`CHAR(` length`)/BINARY(`length`)` | length | A fixed length text string. Any input that is shorter than the length is padded with spaces at the end. All trailing spaces, whether inserted by MySQL or not, are removed when outputting values. MySQL treats text as case-insensitive by default (see the `BINARY` modifier, below). The `BINARY` type is equivalent to `CHAR` with the `BINARY` modifier.
`CHAR(`length`) VARYING/ VARCHAR(` length`)/ VARBINARY(` length`)` | length | A variable length text string (case-insensitive) with a predefined maximum length. The maximum length must be between 1 and 255 characters. Any trailing spaces are removed before storing data of this type. The `VARBINARY` type is equivalent to `VARCHAR` with the `BINARY` modifier.
`TINYTEXT` | length+1 | A text field (case-insensitive) with a maximum length of 255 characters.
`TINYBLOB` | length+1 | A binary field (case-sensitive) with a maximum length of 255 characters. Binary data is case-sensitive.
`TEXT/LONG VARCHAR` | length+2 | A text field with a maximum length of 64KB of text.
`BLOB/LONG VARBINARY` | length+2 | A binary field with a maximum length of 64KB of data.
`MEDIUMTEXT` | length+3 | A text field with a maximum length of 16MB of text.
`MEDIUMBLOB` | length+3 | A binary field with a maximum length of 16MB of data.
`LONGTEXT` | length+4 | A text field with a maximum length of 4GB of text.
`LONGBLOB` | length+4 | A binary field with a maximum length of 4GB of data.
`ENUM` | 1,2 | A field that can contain one of a number of predefined possible values (e.g., `ENUM` ("apples," "oranges," "bananas"). Data may be either entered as one of the text options or as a number corresponding to the index of an option (the first option is number 1). A`NULL` value may always be entered for the field. A maximum of 65535 different options may be defined per enumeration. If there are less than 256 options, the field will take up only one byte of space (otherwise it will use two).
`SET` | 1-8 | A field that can contain any number of a set of predefined possible values (e.g., `SET` ("rock," "pop," "country," "western"). Data may be entered as a comma-separated list of values or as an integer that is the bit representation of the values (e.g., 12, which is 1100 in binary, would correspond to "country, western" in the example above). There is a maximum of 64 values in a single set.

In addition to the main types, several modifiers can also be used to qualify the type:

decimal

  This is the maximum number of decimals allowed in a floating point value. Any values entered that have more decimal places will be rounded off. For example, for the field `price FLOAT(5,2)` the value 4.34 would be displayed as 4.34, the value 234.27 would be displayed as 234.3 (to satisfy the maximum total length) and the value 3.331 would be displayed as 3.33 (to satisfy the maximum decimal length).

length

  For numerical values, this is the number of characters used to display the value to the user. This includes decimal points, signs, and exponent indicators. For example, the field `peanuts INT(4)` has a legal range of -999 to 9999. MySQL will store values outside the given range, however, as long as it is inside the maximum range of the type. If you store a value that is outside the defined range, MySQL will issue a warning, but everything will work normally.

  When used with the `TIMESTAMP` type, the length determines the format used for the timestamp.

  When used with a character type, the length determines the number of characters in the data. For fixed character types, the length is exactly the number of characters used to store the data. For variable characters types, the length is the length of the longest allowed string.

  The length attribute is optional for all types except for `DECIMAL/NUMERIC`, `CHAR` and `VARCHAR`.

  > #### NOTE

  > Since the `DECIMAL/NUMERIC` type is stored as a character string, it is bound by the maximum length the same way a `CHAR` field would be. Therefore, inserting numbers outside of the range defined on the field will fail and generate an error just as if an overly long string were inserted into a `CHAR` field.

precision

  This attribute is available in the `FLOAT` type to provide compatibility with the ODBC system. The value of this attribute can be 4 to define a normal float (same as `FLOAT` without a precision attribute) or 8 to define a double precision float (same as the `DOUBLE` field).

`AUTO_INCREMENT`

  This attribute allows a numeric field to be automatically updated. This is useful for creating unique identification numbers for the rows in the table. Data can be inserted and read normally from an `AUTO_INCREMENT` field, but if a value of `NULL` or is inserted, the existing value of the field is increased by one automatically. The current value of an `AUTO_INCREMENT` field can be obtained by using the `LAST_INSERT_ID` function (see `SELECT`, below).

`BINARY`

  This attribute can be used with `CHAR` and `VARCHAR` types to indicate binary data in the text string. The only effect that `BINARY` has is to make any sorting of the values case-sensitive. By default, MySQL ignores case when sorting text.

`DEFAULT` value

  This attribute assigns a default value to a field. If a row is inserted into the table without a value for this field, this value will be inserted. If a default is not defined, a null value is inserted unless the field is defined as `NOT NULL` in which case MySQL picks a value based on the type of the field.

`NOT NULL`

  This attribute guarantees that every entry in the column will have some non-NULL value. Attempting to insert a `NULL` value into a field defined with NOT NULL will generate an error.

`NULL`

  This attribute specifies that the field is allowed to contain `NULL` values. This is the default if neither this nor the `NOT NULL` modifier are specified. Fields that are contained within an index cannot contain the `NULL` modifier. (It will be ignored, without warning, if it does exist in such a field.)

`PRIMARY KEY`

  This attribute automatically makes the field the primary key (see later) for the table. Only one primary key may exist for a table. Any field that is a primary key must also contain the `NOT NULL` modifier.

`REFERENCES` table [column, . . .)] [`MATCH FULL` | `MATCH PARTIAL`] [`ON DELETE` option ] [`ON UPDATE` option]

  This attribute currently has no effect. MySQL understands the full references syntax but does not implement its behavior. The modifier is included to make it easier to import SQL from different SQL sources. In addition, this functionality may be included in a future release of MySQL.

`UNSIGNED`

  This attribute can be used with integer types to define an unsigned integer. The maximum value of an unsigned integer is twice that of its signed counterpart, but it cannot store negative values. Without any modifiers, all types are considered to be signed.

`ZEROFILL`

  The attribute can be used with integer types to add zeros to the left of every number until the maximum length is reached. For example, the field `counter INT(5) ZEROFILL` would display the number 132 as 00132.

#### Indexes

MySQL supports the concept of an index of a table, as described in Chapter 2, "Database Design". Indexes are created by means of special "types" that are included with the table definition:

`KEY/INDEX` [name] (column, [column2, . . .])

  Creates a regular index of all of the named columns (`KEY` and `INDEX`, in this context, are synonyms). Optionally the index may be given a name. If no name is provided, a name is assigned based on the first column given and a trailing number, if necessary, for uniqueness. If a key contains more than one column, leftmost subsets of those columns are also included in the index. Consider the following index definition.

  >     INDEX idx1 ( name, rank, serial );

  When this index is created, the following groups of columns will be indexed:

  - name, rank, serial

  - name, rank

  - name

`PRIMARY KEY`

  Creates the primary key of the table. A primary key is a special key that can be defined only once in a table. The primary key is a `UNIQUE` key with the name "`PRIMARY`." Despite it's privileged status, in function it is the same as every other unique key.

`UNIQUE` [name] (column, [column2, . . .])

  Creates a special index where every value contained in the index (and therefore in the fields indexed) must be unique. Attempting to insert a value that already exists into a unique index will generate an error. The following would create a unique index of the "nicknames" field:

  >     UNIQUE (nicknames);

> #### NOTE

> In the current implementation of MySQL's indices, `NULL` values are not allowed in any field that is part of an index.

When indexing character fields (`CHAR`, `VARCHAR` and their synonyms only), it is possible to index only a prefix of the entire field. For example, this following will create an index of the numeric field `id' along with the first 20 characters of the character field `address':

>     INDEX adds ( id, address(20) );

When performing any searches of the field `address', only the first 20 characters will be used for comparison unless more than one match is found that contains the same first 20 characters, in which case a regular search of the data is performed. Therefore, it can be a big performance bonus to index only the number of characters in a text field that you know will make the value unique.

Fields contained in an index must be defined with the `NOT NULL` modifier (see earlier). When adding an index as a separate declaration, MySQL will generate an error if `NOT NULL` is missing. However, when defining the primary key by adding the `PRIMARY KEY` modifier to the field definition, the `NOT NULL` modifier is automatically added (without a warning) if it is not explicitly defined.

In addition to the above, MySQL supports the following special "types":

- `FOREIGN KEY` (name (column, [column2, . . . ])

- `CHECK`

These keywords do not actually perform any action. They exist so that SQL exported from other databases can be more easily read into MySQL. Also, some of this missing functionality may be added into a future version of MySQL.

You must have `CREATE` privileges on a database to use the `CREATE TABLE` statement.

#### Examples

>     # Create the new empty database 'employees'
>     CREATE DATABASE employees;
>     CREATE TABLE emp_data ( id INT, name CHAR(50) );
>     # Make the function make_coffee (which returns a string value and is stored
>     # in the myfuncs.so shared library) available to MySQL.
>     CREATE FUNCTION make_coffee RETURNS string SONAME "myfuncs.so";

DELETE |

    DELETE FROM table [WHERE clause]

Deletes rows from a table. When used without a `WHERE` clause, this will erase the entire table and recreate it as an empty table. With a clause, it will delete the rows that match the condition of the clause. This statement returns the number of rows deleted to the user.

As mentioned above, not including a `WHERE` clause will erase this entire table. This is done using an efficient method that is much faster than deleting each row individually. When using this method, MySQL returns to the user because it has no way of knowing how many rows it deleted. In the current design, this method simply deletes all of the files associated with the table except for the file that contains the actual table definition. Therefore, this is a handy method of zeroing out tables with unrecoverably corrupt data files. You will lose the data, but the table structure will still be in place.

You must have `DELETE` privileges on a database to use the following statement:

#### Examples

>     # Erase all of the data (but not the table itself) for the table 'olddata'.
>     DELETE FROM olddata
>     # Erase all records in the 'sales' table where the 'year' field is '1995'.
>     DELETE FROM sales WHERE year=1995

DESCRIBE |

DESC |

    DESCRIBE table[column]
    DESC table[column]

Gives information about a table or column. While this statement works as advertised, its functionality is available (along with much more) in the `SHOW` statement. This statement is included solely for compatibility with Oracle SQL. The optional column name can contain SQL wildcards, in which case information will be displayed for all matching columns.

#### Example

>     # Describe the layout of the table 'messy'
>     DESCRIBE messy
>     # Show the information about any columns starting with 'my_' in the 'big' table.
>     # Remember: '_' is a wildcard, too, so it must be escaped to be used literally.
>     DESC big my\_%

DROP |

    DROP DATABASE name
    DROP INDEX name
    DROP TABLE [IF EXISTS] name[, name2, ...]
    DROP FUNCTION name

Permanently remove a database, table, index, or function from the MySQL system.

`DROP DATABASE`

  Will remove an entire database with all of its associated files. The number of files deleted will be returned to the user. Because three files represent most tables, the number returned is usually the number of tables times three. This is equivalent to running the mysqladmin drop utility. As with running mysqladmin, you must be the administrative user for MySQL (usually root or mysql) to perform this statement.

`DROP INDEX`

  Statement provides for compatibility with other SQL implementations. In older versions of MySQL, this statement does nothing. As of 3.22, this statement is equivalent to `ALTER TABLE` . . . `DROP INDEX`. To perform the `DROP INDEX` statement, you must have `SELECT,` `INSERT`, `DELETE`, `UPDATE`, `CREATE` and `DROP` privileges for the table in question.

`DROP TABLE`

  Will erase an entire table permanently. In the current implementation, MySQL simply deletes the files associated with the table. As of 3.22, you may specify `IF EXISTS` to make MySQL not return an error if you attempt to remove a table that does not exist. You must have `DELETE` privileges on the table to use this statement.

`DROP FUNCTION`

  Will remove a user defined function from the running MySQL server process. This does not actually delete the library file containing the function. You may add the function again at any time using the `CREATE FUNCTION` statement. In the current implementation `DROP FUNCTION` simply removes the function from the function table within the Mysql database. This table keeps track of all active functions.

You must have `DROP` privileges on that table to execute this statement.

> #### WARNING

> `DROP` is by far the most dangerous SQL statement. If you have drop privileges, you may permanently erase a table or even an entire database. This is done without warning or confirmation. The only way to undo a `DROP` is to restore the table or database from backups. The lessons to be learned here are: (1) always keep backups; (2) don't use `DROP` unless you are really sure; and (3) always keep backups.

#### Examples

>     # Completely remove the 'important_data' database from the face of the Earth.
>     DROP DATABASE important_data
>     # Delete the tables 'oh_no', 'help_me' and 'dont_do_it'
>     DROP TABLE oh_no, help_me, dont_do_it
>     # Remove the named index 'my_index'
>     DROP INDEX my_index
>     # Remove the function 'myfunc' from the running server. This can be added again
>     # at anytime using the CREATE FUNCTION statement.
>     DROP FUNCTION myfunc

EXPLAIN |

    EXPLAIN SELECT statement

Displays verbose information about the order and structure of a `SELECT` statement. This can be used to see where keys are not being used efficiently.

#### Example

>     EXPLAIN SELECT customer.name, product.name FROM customer, product, purchases
>     WHERE purchases.customer=customer.id AND purchases.product=product.id

FLUSH |

    FLUSH option[, option...]

Flushes or resets various internal processes depending on the option(s) given. You must have `reload` privileges to execute this statement. The option can be any of the following:

`HOSTS`

  Empties the cache table that stores hostname information for clients. This should be used if a client changes IP addresses, or if there are errors related to connecting to the host.

`LOGS`

  Closes all of the standard log files and reopens them. This can be used if a log file has changed inode number. If no specific extension has been given to the update log, a new update log will be opened with the extension incremented by one.

`PRIVILEGES`

  Reloads all of the internal MySQL permissions grant tables. This must be run for any changes to the tables to take effect.

`STATUS`

  Resets the status variables that keep track of the current state of the server.

`TABLES`

  Closes all currently opened tables and flushes any cached data to disk.

GRANT |

    GRANT privilege [ (column, ...) ] [, privilege [( column, ...) ] ...]
        ON {table} TO user [IDENTIFIED BY 'password']
            [, user [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION]

Previous to MySQL 3.22.11, the `GRANT` statement was recognized but did nothing. In current versions, `GRANT` is functional. This statement will enable access rights to a user (or users). Access can be granted per database, table or individual column. The table can be given as a table within the current database, ``*`' to affect all tables within the current database, ``*.*`' to affect all tables within all databases or ``database.*`' to effect all tables within the given database.

The following privileges are currently supported:

`ALL PRIVILEDGES/ALL`

  Effects all privileges

`ALTER`

  Altering the structure of tables

`CREATE`

  Creating new tables

`DELETE`

  Deleting rows from tables

`DROP`

  Deleting entire tables

`FILE`

  Creating and removing entire databases as well as managing log files

`INDEX`

  Creating and deleting indices from tables

`INSERT`

  Inserting data into tables

`PROCESS`

  Killing process threads

`REFERENCES`

  Not implemented (yet)

`RELOAD`

  Refreshing various internal tables (see the FLUSH statement)

`SELECT`

  Reading data from tables

`SHUTDOWN`

  Shutting down the database server

`UPDATE`

  Altering rows within tables

`USAGE`

  No privileges at all

The `user variable` is of the form user@hostname. Either the user or the hostname can contain SQL wildcards. If wildcards are used, either the whole name must be quoted, or just the part(s) with the wildcards (e.g., `joe@"%.com` `"` and `"`joe@%.com`"` are both valid). A user without a hostname is considered to be the same as user@"%".

If you have a global `GRANT` privilege, you may specify an optional `INDENTIFIED BY` modifier. If the user in the statement does not exist, it will be created with the given password. Otherwise the existing user will have his or her password changed.

Giving the `GRANT` privilege to a user is done with the `WITH GRANT OPTION` modifier. If this is used, the user may grant any privilege they have onto another user.

INSERT |

    INSERT [DELAYED | LOW_PRIORITY ] [INTO]table [ (column, ...) ] VALUES (values )
     [,( values )... ]
    INSERT [LOW_PRIORITY] [INTO] table [ (column, ...) ] SELECT ...
    INSERT [LOW_PRIORITY] [INTO] table SET column=value, column=value,...

Inserts data into a table. The first form of this statement simply inserts the given values into the given columns. Columns in the table that are not given values are set to their default value or `NULL`. The second form takes the results of a `SELECT` query and inserts them into the table. The third form is simply an alternate version of the first form that more explicitly shows which columns correspond with which values. If the `DELAYED` modifier is present in the first form, all incoming `SELECT` statements will be given priority over the insert, which will wait until the other activity has finished before inserting the data. In a similar way, using the `LOW_PRIORITY` modifier with any form of `INSERT` will cause the insertion to be postponed until all other operations from the client have been finished.

When using a `SELECT` query with the `INSERT` statement, you cannot use the `ORDER BY` modifier with the `SELECT` statement. Also, you cannot insert into the same table you are selecting from.

Starting with MySQL 3.22.5 it is possible to insert more than one row into a table at a time. This is done by adding additional value lists to the statement separated by commas.

You must have `INSERT` privileges to use this statement.

#### Examples

>     # Insert a record into the 'people' table.
>     INSERT INTO people ( name, rank, serial_number ) VALUES ( 'Bob Smith',
>         'Captain', 12345 );
>     # Copy all records from 'data' that are older than a certain date into
>     # 'old_data'. This would usually be followed by deleting the old data from
>     # 'data'.
>     INSERT INTO old_data ( id, date, field ) SELECT ( id, date, field) FROM data
>         WHERE date < 87459300;
>     # Insert 3 new records into the 'people' table.
>     INSERT INTO people (name, rank, serial_number ) VALUES ( 'Tim O\'Reilly',
>     'General', 1), ('Andy Oram', 'Major', 4342), ('Randy Yarger', 'Private', 9943);

KILL |

    KILL thread_id

Terminates the specified thread. The thread ID numbers can be found using the `SHOW PROCESSES` statement. Killing threads owned by users other than yourself require `process` privilege.

#### Example

>     # Terminate thread 3
>     KILL 3

LOAD |

    LOAD DATA [LOCAL] INFILE file [REPLACE|IGNORE] INTO TABLE table [delimiters] [(columns)]

Reads a text file that is in a readable format and inserts the data into a database table. This method of inserting data is much quicker than using multiple `INSERT` statements. Although the statement may be sent from all clients just like any other SQL statement, the file referred to in the statement is assumed to be located on the server. If the filename does not have a fully qualified path, MySQL looks under the directory for the current database for the file. As of MySQL 3.22, if the LOCAL modifier is present, the file will be read from the client's local filesystem.

With no delimiters specified, `LOAD DATA INFILE` will assume that the file is tab delimited with character fields, special characters escaped with the backslash (\), and lines terminated with a newline character.

In addition to the default behavior, you may specify your own delimiters using the following keywords:

`FIELDS TERMINATED BY` '`c`'

  Specifies the character used to delimit the fields. Standard C language escape codes can be used to designate special characters. This value may contain more than one character. For example, `FIELDS TERMINATED BY` '`,`' denotes a comma delimited file and `FIELDS TERMINATED BY` '`\t`' denotes tab delimited. The default value is tab delimited.

`FIELDS ENCLOSED BY` '`c`'

  Specifies the character used to enclose character strings. For example, `FIELD ENCLOSED BY` '`"`' would mean that a line containing `"this, value", "this", "value"` would be taken to have three fields: `"this,value", "this", and "value"`. The default behavior is to assume that no quoting is used in the file.

`FIELDS ESCAPED BY` '`c`'

  Specifies the character used to indicate that the next character is not special, even though it would usually be a special character. For example, with `FIELDS ESCAPED BY` '`^`' a line consisting of `First,Second^,Third,Fourth` would be parsed as three fields: `"First", "Second,Third" and "Fourth"`. The exceptions to this rule are the null characters. Assuming the `FIELDS ESCAPED BY` value is a backslash, `\0` indicates an ASCII NULL (character number 0) and `\N` indicates a MySQL null value. The default value is the backslash character. Note that MySQL itself considers the backslash character to be special. Therefore to indicate backslash in that statement you must backslash the backslash like this: `FIELDS ESCAPED BY` '`\\`'.

`LINES TERMINATED BY` '`c`'

  Specifies the character that indicates the start of a new record. This value can contain more than one character. For example, with `LINES TERMINATED BY` '`.`', a file consisting of `a,b,c.d,e,f.g,h,k.` would be parsed as three separate records, each containing three fields. The default is the newline character. This means that by default, MySQL assumes that each line is a separate record.

The keyword `FIELDS` should only be used for the entire statement. For example:

>     LOAD DATA INFILE data.txt FIELDS TERMINATED BY ',' ESCAPED BY '\\'.

By default, if a value read from the file is the same as an existing value in the table for a field that is part of a unique key, an error is given. If the `REPLACE` keyword is added to the statement, the value from the file will replace the one already in the table. Conversely, the `IGNORE` keyword will cause MySQL to ignore the new value and keep the old one.

The word `NULL` encountered in the data file is considered to indicate a null value unless the `FIELDS ENCLOSED BY` character encloses it.

Using the same character for more than one delimiter can confuse MySQL. For example, `FIELDS TERMINATED BY` ' `,` ' `ENCLOSED BY` ' `,` ' would produce unpredictable behavior.

If a list of columns is provided, the data is inserted into those particular fields in the table. If no columns are provided, the number of fields in the data must match the number of fields in the table, and they must be in the same order as the fields are defined in the table.

You must have `SELECT` and `INSERT` privileges on the table to use this statement.

#### Example

>     # Load in the data contained in 'mydata.txt' into the table 'mydata'. Assume
>     # that the file is tab delimited with no quotes surrounding the fields.
>     LOAD DATA INFILE 'mydata.txt' INTO TABLE mydata
>     # Load in the data contained in 'newdata.txt' Look for two comma delimited
>     # fields and insert their values into the fields 'field1' and 'field2' in
>     # the 'newtable' table.
>     LOAD DATA INFILE 'newdata.txt' INTO TABLE newtable FIELDS TERMINATED BY ','
>        ( field1, field2 )

LOCK |

    LOCK TABLES name [AS alias] READ|WRITE [, name2 [AS alias] READ|WRITE, ...]

Locks a table for the use of a specific thread. This command is generally used to emulate transactions as described in Chapter 7, "Other Mid-Range Database Engines". If a thread creates a `READ` lock all other threads may read from the table but only the controlling thread can write to the table. If a thread creates a `WRITE` lock, no other thread may read from or write to the table.

> #### WARNING

> Using locked and unlocked tables at the same time can cause the process thread to freeze. You must lock all of the tables you will be accessing during the time of the lock. Tables you access only before or after the lock do not need to be locked. The newest versions of MySQL generate an error if you attempt to access an unlocked table while you have other tables locked.

#### Example

>     # Lock tables 'table1' and 'table3' to prevent updates, and block all access
>     # to 'table2'. Also create the alias 't3' for 'table3' in the current thread.
>     LOCK TABLES table1 READ, table2 WRITE, table3 AS t3 READ

OPTIMIZE |

    OPTIMIZE TABLE name

Recreates a table eliminating any wasted space. This is done by creating the optimized table as a separate, temporary table and then moving over to replace the current table. While the procedure is happening, all table operations continue as normal (all writes are diverted to the temporary table).

#### Example

>     OPTIMIZE TABLE mytable

REPLACE |

    REPLACE INTO table [( column, ...)] VALUES ( value , ...)
    REPLACE INTO table [(column, ...)] SELECT select_clause

Inserts data to a table, replacing any old data that conflicts. This statement is identical to `INSERT` except that if a value conflicts with an existing unique key, the new value replaces the old one. The first form of this statement simply inserts the given values into the given columns. Columns in the table that are not given values are set to their default value or `NULL`. The second form takes the results of a `SELECT` query and inserts them into the table.

#### Examples

>     # Insert a record into the 'people' table.
>     REPLACE INTO people ( name, rank, serial_number ) VALUES ( 'Bob Smith',
>         'Captain', 12345 )
>     # Copy all records from 'data' that are older than a certain date into
>     # 'old_data'. This would usually be followed by deleting the old data from
>     # 'data'.
>     REPLACE INTO old_data ( id, date, field ) SELECT ( id, date, field) FROM data
>         WHERE date < 87459300

REVOKE |

    REVOKE privilege [(column, ...)] [, privilege [(column, ...) ...]
       ON table FROM user

Removes a privilege from a user. The values of privilege, table, and user are the same as for the GRANT statement. You must have the GRANT privilege to be able to execute this statement.

SELECT |

    SELECT [STRAIGHT_JOIN] [DISTINCT|ALL] value[, value2...]
    [INTO OUTFILE 'filename' delimiters] FROM table[, table2...] [clause]

Retrieve data from a database. The `SELECT` statement is the primary method of reading data from database tables.

If you specify more than one table, MySQL will automatically join the tables so that you can compare values between the tables. In cases where MySQL does not perform the join in an efficient manner, you can specify `STRAIGHT_JOIN` to force MySQL to join the tables in the order you enter them in the query.

If the `DISTINCT` keyword is present, only one row of data will be output for every group of rows that is identical. The `ALL` keyword is the opposite of distinct and displays all returned data. The default behavior is `ALL`.

The returned values can be any one of the following:

Aliases

  Any complex column name or function can be simplified by creating an alias for it. The value can be referred to by its alias anywhere else in the `SELECT` statement (e.g., `SELECT DATE_FORMAT(date,"%W, %M %d %Y") as nice_date FROM calendar`).

Column names

  These can be specified as `column`, `table.column` or `database.table.column`. The longer forms are necessary only to disambiguate columns with the same name, but can be used at any time (e.g., `SELECT name FROM people; SELECT mydata.people.name FROM people)`.

Functions

  MySQL supports a wide range of built-in functions (see later). In addition, user defined functions can be added at any time using the `CREATE FUNCTION` statement (e.g., `SELECT COS(angle) FROM triangle`).

By default, MySQL sends all output to the client that sent the query. It is possible however, to have the output redirected to a file. In this way you can dump the contents of a table (or selected parts of it) to a formatted file that can either be human readable, or formatted for easy parsing by another database system.

The `INTO OUTFILE` '`filename`'`delimiters` modifier is the means in which output redirection is accomplished. With this the results of the `SELECT` query are put into `filename`. The format of the file is determined by the `delimiters` arguments, which are the same as the `LOAD DATA INFILE` statement with the following additions:

- The `OPTIONALLY` keyword may be added to the `FIELDS ENCLOSED BY` modifier. This will cause MySQL to thread enclosed data as strings and non-enclosed data as numeric.

- Removing all field delimiters (i.e., `FIELDS TERMINATED BY` ''`ENCLOSED BY` '') will cause a fixed-width format to be used. Data will be exported according to the display size of each field. Many spreadsheets and desktop databases can import fixed-width format files.

The default behavior with no delimiters is to export tab delimited data using backslash (\) as the escape character and to write one record per line.

The list of tables to join may be specified in the following ways:

Table1, Table2, Table3, . . .

  This is the simplest form. The tables are joined in the manner that MySQL deems most efficient. This method can also be written as `Table1 JOIN Table2 JOIN Table3, ....` The`CROSS` keyword can also be used, but it has no effect (e.g., `Table1` `CROSS JOIN Table2)` Only rows that match the conditions for both columns are included in the joined table. For example, `SELECT * FROM people, homes WHERE people.id=homes.owner` would create a joined table containing the rows in the `people` table that have `id` fields that match the `owner` field in the `homes` table.

  > #### NOTE

  > Like values, table names can also be aliased (e.g., `SELECT` t1.name, t2.address `FROM` long_table_name t1, longer_table_name t2)

Table1 `STRAIGHT_JOIN` Table2

  This is identical to the earlier method, except that the left table is always read before the right table. This should be used if MySQL performs inefficient sorts by joining the tables in the wrong order.

Table1 `LEFT [OUTER] JOIN` Table2 `ON` clause

  This checks the right table against the clause. For each row that does not match, a row of `NULL`s is used to join with the left table. Using the previous example `SELECT * FROM people, homes LEFT JOIN people, homes ON people.id=homes.owner`, the joined table would contain all of the rows that match in both tables, as well as any rows in the `people` table that do not have matching rows in the `homes` table, `NULL` values would be used for the `homes` fields in these rows. The `OUTER` keyword is optional and has no effect.

Table1 `LEFT [OUTER] JOIN` Table2 `USING` (column[, column2 . . .])

  This joins the specified columns only if they exist in both tables (e.g., `SELECT * FROM old LEFT OUTER JOIN new USING (id)`)

Table1 `NATURAL LEFT [OUTER] JOIN` Table2

  This joins only the columns that exist in both tables. This would be the same as using the previous method and specifying all of the columns in both tables (e.g., `SELECT rich_people.salary, poor_people.salary FROM rich_people NATURAL LEFT JOIN poor_people`)

{ oj Table1 `LEFT OUTER JOIN` Table2 `ON` clause }

  This is identical to Table1 `LEFT JOIN` Table2 `ON` clause and is only included for ODBC compatibility. (The "oj" stands for "Outer Join".)

If no clause is provided, `SELECT` returns all of the data in the selected table(s).

The search clause can contain any of the following substatements:

`WHERE` statement

  The `WHERE` statement construct is the most common way of searching for data in SQL. This statement is usually a comparison of some type but can also include any of the functions listed below, except for the aggregate functions. Named values, such as column names and aliases, and literal numbers and strings can be used in the statement. The following operators are supported:

  ( )

    Parentheses are used to group operators in order to force precedence.

  +

    Adds two numerical values

  -

    Subtracts two numerical values

  *

    Multiplies two numerical values

  /

    Divides two numerical values

  %

    Gives the modulo of two numerical values

  |

    Performs a bitwise OR on two integer values

  &

    Performs a bitwise AND on two integer values

  <<

    Performs a bitwise left shift on an integer value

  >>

    Performs a bitwise right shift on an integer value

  `NOT` or !

    Performs a logical `NOT` (returns 1 if the value is 0 and returns 0 otherwise).

  `OR` or ||

    Performs a logical `OR` (returns 1 if any of the arguments are not 0, otherwise returns 0)

  `AND` or &&

    Performs a logical `AND` (returns 0 if any of the arguments are 0, otherwise returns 1)

  =

    Match rows if the two values are equal. MySQL automatically converts between types when comparing values.

  <> or !=

    Match rows if the two values are not equal.

  <=

    Match rows if the left value is less than or equal to the right value.

  <

    Match rows if the left value is less than the right value.

  >=

    Match rows if the left value is greater than or equal to the right value.

  >

    Match rows if the left value is greater than the right value.

value `BETWEEN` value1 `AND` value2

  Match rows if value is between value1 and value2, or equal to one of them.

value `IN` `(`value1,value2,`...)`

  Match rows if value is among the values listed.

value `NOT IN` `(`value1, value2,`...)`

  Match rows if value is not among the values listed.

value1 `LIKE` value2

  Compares value1 to value2 and matches the rows if they match. The right-hand value can contain the wildcard '%' which matches any number of characters (including 0) and '_' which matches exactly one character. This is probably the single most used comparison in SQL. The most common usage is to compare a field value with a literal containing a wildcard (e.g., `SELECT name FROM people WHERE name LIKE` '`B%`').

value1 `NOT LIKE` value2

  Compares value1 to value2 and matches the rows if they differ. This is identical to `NOT (value1 LIKE value2)`.

value1 `REGEXP/RLIKE` value2

  Compares value1 to value2 using the extended regular expression syntax and matches the rows if they match. The right hand value can contain full Unix regular expression wildcards and constructs (e.g., `SELECT name FROM people WHERE name RLIKE` '`^B.*`').

value1 `NOT REGEXP` value2

  Compares value1 to value2 using the extended regular expression syntax and matches the rows if they differ. This is identical to `NOT (value1 REXEXP value2)`.

  The `WHERE` clause returns any of the expression values that are not or `NULL` (that is, anything that is not logically false). Therefore, `SELECT age FROM people WHERE age>10` will return only those ages that are greater than 10.

`GROUP BY` column`[`, column2,...`]`

  This gathers all of the rows together that contain data from a certain column. This allows aggregate functions to be performed upon the columns (e.g., `SELECT name,MAX(age) FROM people GROUP BY name)`.

`HAVING` clause

  This is the same as a `WHERE` clause except that it is performed upon the data that has already been retrieved from the database. The `HAVING` statement is a good place to perform aggregate functions on relatively small sets of data that have been retrieved from large tables. This way, the function does not have to act upon the whole table, only the data that has already been selected (e.g., `SELECT name,MAX(age) FROM people GROUP BY name HAVING MAX(age)>80`).

`ORDER BY` column `[`ASC`|`DESC`][`, column2 `[`ASC`|`DESC`]`,...`]`

  Sorts the returned data using the given column(s). If `DESC` is present, the data is sorted in descending order, otherwise ascending order is used. Ascending order can also be explicitly stated with the ASC keyword (e.g., `SELECT name, age FROM people ORDER BY age DESC`).

`LIMIT` `[`start,`]` rows

  Returns Only the specified number of rows. If the `start` value is supplied, that many rows are skipped before the data is returned. The first row is number (e.g., `SELECT url FROM links LIMIT 5,10` (returns URL's numbered 5 through 14).

`PROCEDURE` name

  In mSQL and early versions of MySQL, this does not do anything. It was provided to make importing data from other SQL servers easier. Starting with MySQL 3.22, this substatement lets you specify a procedure that modifies the query result before returning it to the client.

`SELECT` supports the concept of functions. MySQL defines several built-in functions that can operate upon the data in the table, returning the computed value(s) to the user. With some functions, the value returned depends on whether the user wants to receive a numerical or string value. This is regarded as the "context" of the function. When selecting values to be displayed to the user, only text context is used, but when selecting data to be inserted into a field, or to be used as the argument of another function, the context depends upon what the receiver is expecting. For instance, selecting data to be inserted into a numerical field will place the function into a numerical context.

The following are all of the named functions built into MySQL:

`ABS(` number`)`

  Returns the absolute value of number (e.g., `ABS(-10)` returns 10).

`ACOS(` number`)`

  Returns the inverse cosine of `number` in radians (e.g., `ACOS(0)` returns 1.570796).

`ASCII(` char`)`

  Returns the ASCII value of the given character (e.g., ASCII(`h') returns 104).

`ASIN(` number`)`

  Returns the inverse sine of number in radians (e.g., `ASIN(0)` returns 0.000000).

`ATAN(` number`)`

  Returns the inverse tangent of number in radians (e.g., `ATAN(1)` returns 0.785398.)

`ATAN2(` X, Y`)`

  Returns the inverse tangent of the point `(`X`,`Y`)` (e.g., `ATAN(-3,3)` returns -0.785398).

`CHAR(` num1`[,`num2,`. . .])`

  Returns a string made from converting each of the numbers to the character corresponding to that ASCII value (e.g., `CHAR(122)` returns `z').

`CONCAT(` string1,string2`[`,string3,`. . .])`

  Returns the string formed by joining together all of the arguments (e.g., `CONCAT(`'`Hi`'`,`'`` '`,`'`Mom`'`,`'`!`'`)` returns "Hi Mom!").

`CONV(` number, base1, base2`)`

  Returns the value of number converted from base1 to base2. Number must be an integer value (either as a bare number or as a string). The bases can be any integer from 2 to 36 (e.g., `CONV(8,10,2)` returns 1000 (the number 8 in decimal converted to binary)).

`BIN(` decimal`)`

  Returns the binary value of the given decimal number. This is equivalent to the function `CONV(decimal,10,2)` (e.g., `BIN(8)` returns 1000).

`BIT_COUNT(`number`)`

  Returns the number of bits that are set to 1 in the binary representation of the number (e.g., `BIT_COUNT(17)` returns 2).

`CEILING(` number`)`

  Returns the smallest integer larger than or equal to number (e.g., `CEILING (5.67)` returns 6).

`COS(` radians`)`

  Returns the cosine of the given number, which is in radians (e.g., `COS(0)` returns 1.000000).

`COT(` radians`)`

  Returns the cotangent of the given number, which must be in radians (e.g.,`COT(1)` returns 0.642093).

`CURDATE()/CURRENT_DATE()`

  Returns the current date. A number of the form `YYYYMMDD` is returned if this is used in a numerical context, otherwise a string of the form '`YYYY-MM-DD`' is returned (e.g., `CURDATE()` could return "1998-08-24").

`CURTIME()`/`CURRENT_TIME()`

  Returns the current time. A number of the form `HHMMSS` is returned if this is used in a numerical context, otherwise a string of the form `HH:MM:SS` is returned (e.g., `CURRENT_TIME()` could return 13:02:43).

`DATABASE()`

  Returns the name of the current database (e.g., `DATABASE()` could return "mydata").

`DATE_ADD(`date, `INTERVAL` amount type`)/ADDDATE(`date, `INTERVAL` amount type`)`

  Returns a date formed by adding the given amount of time to the given date. The type of time to add can be one of the following: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, `YEAR`, `MINUTE_SECOND` (as "minutes:seconds"), `HOUR_MINUTE` (as "hours:minutes"), `DAY_HOUR` (as "days hours"), `YEAR_MONTH` (as "years-months"), `HOUR_SECOND` (as "hours:minutes:seconds"), `DAY_MINUTE` (as "days hours:minutes") and `DAY_SECOND` (as "days hours:minutes:seconds"). Except for those types with forms specified above, the amount must be an integer value (e.g., `DATE_ADD("1998-08-24 13:00:00", INTERVAL 2 MONTH`) returns "1998-10-24 13:00:00").

`DATE_FORMAT(`date, format`)`

  Returns the date formatted as specified. The format string prints as given with the following values substituted:

  %a

    Short weekday name (Sun, Mon, etc.)

  %b

    Short month name (Jan, Feb, etc.)

  %D

    Day of the month with ordinal suffix (1st, 2nd, 3rd, etc.)

  %d

    Day of the month

  %H

    24-hour hour (always two digits, e.g., 01)

  %h/%I

    12-hour hour (always two digits, e.g., 09)

  %i

    Minutes

  %j

    Day of the year

  %k

    24-hour hour (one or two digits, e.g., 1)

  %l

    12-hour hour (one or two digits, e.g., 9)

  %M

    Name of the month

  %m

    Number of the month (January is 1).

  %p

    AM or PM

  %r

    12-hour total time (including AM/PM)

  %S

    Seconds (always two digits, e.g., 04)

  %s

    Seconds (one or two digits, e.g., 4)

  %T

    24-hour total time

  %U

    Week of the year (new weeks begin on Sunday)

  %W

    Name of the weekday

  %w

    Number of weekday (0 is Sunday)

  %Y

    Four digit year

  %y

    Two digit year

  %%

    A literal "%" character.

`DATE_SUB(`date, `INTERVAL` amount type`)/SUBDATE(`date, `INTERVAL` amount type`)`

  Returns a date formed by subtracting the given amount of time from the given date. The same interval types are used as with `DATE_ADD` (e.g., `SUBDATE("1999-05-20 11:04:23", INTERVAL 2 DAY`) returns "1999-05-18 11:04:23").

`DAYNAME(` date`)`

  Returns the name of the day of the week for the given date (e.g.,`DAYNAME(`'`1998-08-22`'`)` returns "Saturday").

`DAYOFMONTH(`date`)`

  Returns the day of the month for the given date (e.g., `DAYOFMONTH(`'`1998-08-22`'`)` returns 22).

`DAYOFWEEK(`date`)/WEEKDAY(`date`)`

  Returns the number of the day of the week (1 is Sunday) for the given date (e.g., `DAY_OF_WEEK(`'`1998-08-22`'`)` returns 7).

`DAYOFYEAR(`date`)`

  Returns the day of the year for the given date (e.g., `DAYOFYEAR(`'`1983-02-15`'`)` returns 46).

`DEGREES(`radians`)`

  Returns the given argument converted from radians to degrees (e.g., `DEGREES(2*PI())` returns 360.000000).

`ELT(` number,string1,string2,`` . . .`)`

  Returns string1 if number is 1, string2 if number is 2, etc. A null value is returned if number does not correspond with a string (e.g.,`ELT(3, "once","twice","thrice","fourth")` returns "thrice").

`ENCRYPT(` string`[`, salt`])`

  Password-encrypts the given string. If a salt is provided, it is used to generate the password (e.g., `ENCRYPT(`'`mypass`'`,`'`3a`'`)` could return "3afi4004idgv").

`EXP(` power`)`

  Returns the number e raised to the given power (e.g., `EXP(1)` returns 2.718282).

`FIELD(` string,string1,string2,`` . . .`)`

  Returns the position in the argument list (starting with string1) of the first string that is identical to string. Returns if no other string matches string (e.g., `FIELD(`'`abe`'`,`'`george`'`,`'`john`'`,`'`abe`'`,`'`bill`'`)` returns).

`FIND_IN_SET(`string,set`)`

  Returns the position of string within set. The set argument is a series of strings separated by commas (e.g., `FIND_IN_SET (`'`abe`'`,` '`george, john, abe, bill`'`)` returns 3).

`FLOOR(` number`)`

  Returns the largest integer smaller than or equal to number (e.g., `FLOOR (5.67)` returns 5).

`FORMAT(` number, decimals`)`

  Neatly formats the given number, using the given number of decimals (e.g.,`FORMAT(4432.99134,2)` returns "4,432.99").

`FROM_DAYS(` days`)`

  Returns the date that is the given number of days (where day 1 is the Jan 1 of year 1) (e.g., `FROM_DAYS(728749)` returns "1995-04-02").

`FROM_UNIXTIME(` seconds`[`,`` format`])`

  Returns the date (in `GMT`) corresponding to the given number of seconds since the epoch (January 1, 1970 `GMT`). If a format string (using the same format as `DATE_FORMAT`) is given, the returned time is formatted accordingly (e.g.,`FROM_UNIXTIME(903981584)` returns "1998-08-24 18:00:02").

`GET_LOCK(` name,seconds`)`

  Creates a named user-defined lock that waits for the given number of seconds until timeout. This lock can be used for client-side application locking between programs that cooperatively use the same lock names. If the lock is successful, 1 is returned. If the lock times out while waiting, is returned. All others errors return a `NULL` value. Only one named lock may be active at a time for a singe session. Running `GET_LOCK()` more than once will silently remove any previous locks (e.g., `GET_LOCK("mylock",10)` could return 1 within the following 10 seconds).

`GREATEST(` num1, num2`[`, num3,`. . . ])`

  Returns the numerically largest of all of the arguments (e.g., `GREATEST(5,6,68,1,4)` returns 68).

`HEX(` decimal`)`

  Returns the hexadecimal value of the given decimal number. This is equivalent to the function CONV(decimal,10,16) (e.g., HEX(90) returns "3a").

`HOUR(` time`)`

  Returns the hour of the given time (e.g., `HOUR(`'`15:33:30`'`)` returns 15).

`IF(` test, value1, value2`)`

  If test is true, returns value1, otherwise returns value2. The test value is considered to be an integer, therefore floating point values must be used with comparison operations to generate an integer (e.g., `IF(1>0,"true","false")` returns `true`).

`IFNULL(` value, value2`)`

  Returns value if it is not null, otherwise returns value2 (e.g., `IFNULL(NULL, "bar")` returns "bar").

`INSERT(` string,position,length,new`)`

  Returns the string created by replacing the substring of string starting at position and going length characters with new (e.g., `INSERT(`'`help`'`,3,1,`'`can jum`'`)` returns "he can jump").

`INSTR(` string,substring`)`

  Identical to `LOCATE` except that the arguments are reversed (e.g., `INSTR(`'`makebelieve`'`,`'`lie`'`)` returns 7).

`ISNULL(` expression`)`

  Returns 1 if the expression evaluates to `NULL`, otherwise returns (e.g.,`ISNULL(3)` returns 0).

`INTERVAL(`A,B,C,D,`. . . )`

  Returns if A is the smallest value, 1 if A is between B and C, 2 if A is between C and D, etc. All of the values except for A must be in order (e.g.,`INTERVAL(5,2,4,6,8)` returns 2 (because 5 is in the second interval, between 4 and 6).

`LAST_INSERT_ID()`

  Returns the last value that was automatically generated for an `AUTO_INCREMENT` field (e.g., `LAST_INSERT_ID()` could return 4).

`LCASE(` string`)/LOWER(`string`)`

  Returns string with all characters turned into lower case (e.g., `LCASE(`'`BoB`'`)` returns "bob").

`LEAST(` num1, num2`[`, num3, `. . .])`

  Returns the numerically smallest of all of the arguments (e.g., `LEAST(5,6,68,1,4)` returns 1).

`LEFT(` string,length`)`

  Returns length characters from the left end of string (e.g.,`LEFT("12345",3)` returns "123").

`LENGTH(` string`)/OCTET_LENGTH(` string`)/CHAR_LENGTH(` string`)/CHARACTER_LENGTH(` string`)`

  Returns the length of string (e.g., `CHAR_LENGTH(`'`Hi Mom!`'`)` returns 7). In character sets that use multibyte characters (such as Unicode, and several Asian character sets), one character may take up more than one byte. In these cases, MySQL's string functions should correctly count the number of characters, not bytes, in the string. However, in versions prior to 3.23, this did not work properly and the function returned the number of bytes.

`LOCATE(` substring,string`[`,number`])/POSITION(`substring,string`)`

  Returns the character position of the first occurrence of substring within string. If substring does not exist in string, is returned. If a numerical third argument is supplied to `LOCATE`, the search for substring within string does not start until the given position within string (e.g.,`LOCATE(`'`SQL`'`,`'`MySQL`'`)` returns 3).

`LOG(` number`)`

  Returns the natural logarithm of number (e.g., `LOG(2)` returns 0.693147).

`LOG10(` number`)`

  Returns the common logarithm of number (e.g., `LOG10(1000)` returns 3.000000).

`LPAD(` string,length,padding`)`

  Returns string with `padding` added to the left end until the new string is length characters long (e.g., `LPAD(`'`Merry X-Mas`'`,18,`'`Ho`'`)` returns "HoHoHo Merry X-Mas").

`LTRIM(` string`)`

  Returns string with all leading whitespace removed (e.g.,`LTRIM(`'`Oops`'`)` returns "Oops").

`MID(` string,position,length`)/SUBSTRING(`string,position,length`)/SUBSTRING(`string `FROM` position `FOR` length`)`

  Returns the substring formed by taking length characters from string, starting at position (e.g., `SUBSTRING(`'`12345`'`,2,3)` returns "234").

`MINUTE(` time`)`

  Returns the minute of the given time (e.g., `MINUTE(`'`15:33:30`'`)` returns 33).

`MOD(` num1, num2`)`

  Returns the modulo of num1 divided by num2. This is the same as the % operator (e.g., `MOD(11,3)` returns 2).

`MONTH(` date`)`

  Returns the number of the month (1 is January) for the given date (e.g., `MONTH`(`1998-08-22') returns 8).

`MONTHNAME(` date`)`

  Returns the name of the month for the given date (e.g., `MONTHNAME(`'`1998-08-22`'`)` returns "August").

`NOW()/SYSDATE()/CURRENT_TIMESTAMP()`

  Returns the current date and time. A number of the form `YYYYMMDDHHMMSS` is returned if this is used in a numerical context, otherwise a string of the form '`YYYY-MM-DD HH:MM:SS`' is returned (e.g., `SYSDATE()` could return "1998-08-24 12:55:32").

`OCT(` decimal`)`

  Returns the octal value of the given decimal number. This is equivalent to the function `CONV(decimal,10,8)` (e.g.,`OCT(8)` returns 10).

`PASSWORD(` string`)`

  Returns a password-encrypted version of the given string (e.g.,`PASSWD(`'`mypass`'`)` could return "3afi4004idgv").

`PERIOD_ADD(` date,months`)`

  Returns the date formed by adding the given number of months to date (which must be of the form `YYMM` or `YYYYMM`) (e.g.,`PERIOD_ADD(9808,14)` returns 199910).

`PERIOD_DIFF(` date1, date2`)`

  Returns the number of months between the two dates (which must be of the form `YYMM` or `YYYYMM`) (e.g., `PERIOD_DIFF(199901,8901)` returns 120).

`PI()`

  Returns the value of pi: 3.141593.

`POW(` num1, num2`)/POWER(`num1, num2`)`

  Returns the value of num1 raised to the num2 power (e.g., `POWER(3,2)` returns 9.000000).

`QUARTER(` date`)`

  Returns the number of the quarter of the given date (1 is January-March) (e.g.,`QUARTER(`'`1998-08-22`'`)` returns 3).

`RADIANS(` degrees`)`

  Returns the given argument converted from degrees to radians (e.g., `RADIANS(-90)` returns -1.570796).

`RAND([` seed`])`

  Returns a random decimal value between and 1. If an argument is specified, it is used as the seed of the random number generator (e.g., `RAND(3)` could return 0.435434).

`RELEASE_LOCK(` name`)`

  Removes the named locked created with the `GET_LOCK` function. Returns 1 if the release is successful, if it failed because the current thread did not own the lock and a `null` value if the lock did not exist (e.g., `RELEASE_LOCK("mylock")`).

`REPEAT(` string,number`)`

  Returns a string consisting of the original string repeated number times. Returns an empty string if number is less than or equal to zero (e.g.,`REPEAT(`'`ma`'`,4)` returns `mamamama').

`REPLACE(` string,old,new`)`

  Returns a string that has all occurrences of the substring old replaced with new (e.g., `REPLACE`('black jack','ack','oke') returns "bloke joke").

`REVERSE(` string`)`

  Returns the character reverse of string (e.g., `REVERSE(`'`my bologna`'`)` returns "angolob ym").

`RIGHT(`string,length`)/SUBSTRING(` string `FROM` length`)`

  Returns length characters from the right end of string (e.g., `SUBSTRING`("12345" FROM 3) returns "345").

`ROUND(` number`[`,decimal`])`

  Returns number, rounded to the given number of decimals. If no decimal argument is supplied, number is rounded to an integer (e.g., `ROUND(5.67,1)` returns 5.7).

`RPAD(` string,length,padding`)`

  Returns string with padding added to the right end until the new string is length characters long (e.g., `RPAD(`'`Yo`'`,5,`'`!`'`)` returns "Yo!!!").

`RTRIM(` string`)`

  Returns string with all trailing whitespace removed (e.g., `RTRIM(`'`Oops` '`)` returns "Oops").

`SECOND(` time`)`

  Returns the seconds of the given time (e.g., `SECOND(`'`15:33:30`'`)` returns 30).

`SEC_TO_TIME(` seconds`)`

  Returns the number of hours, minutes and seconds in the given number of seconds. A number of the form `HHMMSS` is returned if this is used in a numerical context, otherwise a string of the form `HH:MM:SS` is returned (e.g.,`SEC_TO_TIME(3666)` returns "01:01:06").

`SIGN(` number`)`

  Returns -1 if number is negative, if it's zero, or 1 if it's positive (e.g., `SIGN(4)` returns 1).

`SIN(` radians`)`

  Returns the sine of the given number, which is in radians (e.g., `SIN(2*PI())` returns 0.000000).

`SOUNDEX(` string`)`

  Returns the Soundex code associated with string (e.g., `SOUNDEX(`'`Jello`'`)` returns "J400").

`SPACE(` number`)`

  Returns a string that contains number spaces (e.g., `SPACE(5)` returns "`` ").

`SQRT(`number`)`

  Returns the square root of number (e.g., `SQRT(16)` returns 4.000000).

`STRCMP(` string1, string2`)`

  Returns if the strings are the same, -1 if string1 would sort before than string2, or 1 if string1 would sort after than string2 (e.g., `STRCMP(`'`bob`'`,`'`bobbie`'`)` returns -1).

`SUBSTRING_INDEX(` string,character,number`)`

  Returns the substring formed by counting number of character within string and then returning everything to the right if count is positive, or everything to the left if count is negative (e.g., `SUBSTRING_INDEX(`'`1,2,3,4,5`'`,`'`,`'`,-3)` returns "1,2,3").

`SUBSTRING(` string,position`)`

  Returns all of string starting at position characters (e.g., `SUBSTRING`("123456",3) returns "3456").

`TAN(` radians`)`

  Returns the tangent of the given number, which must be in radians (e.g.,`TAN(0)` returns 0.000000).

`TIME_FORMAT(` time, format`)`

  Returns the given time using a format string. The format string is of the same type as `DATE_FORMAT`, as shown earlier.

`TIME_TO_SEC(` time`)`

  Returns the number of seconds in the time argument (e.g., `TIME_TO_SEC(`'`01:01:06`'`)` returns 3666).

`TO_DAYS(` date`)`

  Returns the number of days (where day 1 is the Jan 1 of year 1) to the given date. The date may be a value of type `DATE`, `DATETIME` or `TIMESTAMP,` or a number of the form `YYMMDD` or `YYYYMMDD` (e.g., `TO_DAYS(19950402)` returns 728749).

`TRIM([BOTH|LEADING|TRAILING] [` remove`] [FROM]` string`)`

  With no modifiers, returns string with all trailing and leading whitespace removed. You can specify whether to remove either the leading or the trailing whitespace, or both. You can also specify another character other than space to be removed (e.g., `TRIM(both` '`-`'`from` '`---look here---`'`)` returns "look here").

`TRUNCATE` (number, decimals)

  Returns number truncated to the given number of decimals (e.g., `TRUNCATE(3.33333333,2)` returns 3.33).

`UCASE(` string`)/UPPER(`string`)`

  Returns string with all characters turned into uppercase (e.g.,`UPPER (`'`Scooby`'`)` returns "`SCOOBY`").

`UNIX_TIMESTAMP([` date`])`

  Returns the number of seconds from the epoch (January 1, 1970 `GMT`) to the given date (in `GMT`). If no date is given, the number of seconds to the current date is used (e.g., `UNIX_TIMESTAMP(`'`1998-08-24 18:00:02`'`)` returns 903981584).

`USER()/SYSTEM_USER()/SESSION_USER()`

  Returns the name of the current user (e.g., `SYSTEM_USER()` could return "ryarger").

`VERSION()`

  Returns the version of the MySQL server itself (e.g., `VERSION()` could return "3.22.5c-alpha").

`WEEK(` date`)`

  Returns the week of the year for the given date (e.g., `WEEK(`'`1998-12-29`'`)` returns 52).

`YEAR(` date`)`

  Returns the year of the given date (e.g., `YEAR(`'`1998-12-29`'`)` returns 1998).

The following functions are aggregate functions that perform upon a set of data. The usual method of using these is to perform some action on a complete set of returned rows. For example, `SELECT AVG(height) FROM kids` would return the average of all of the values of the '`height`' field in the `kids` table.

`AVG(` expression`)`

  Returns the average value of the values in expression (e.g., `SELECT AVG(score) FROM tests`).

`BIT_AND(` expression`)`

  Returns the bitwise `AND` aggregate of all of the values in expression (e.g.,`SELECT BIT_AND(flags) FROM options)`.

`BIT_OR(` expression`)`

  Returns the bitwise `OR` aggregate of all of the values in expression (e.g., `SELECT BIT_OR(flags) FROM options`).

`COUNT(` expression`)`

  Returns the number of times expression was not null. `COUNT(*)` will return the number of rows with some data in the entire table (e.g., `SELECT COUNT(*) FROM folders`).

`MAX(` expression`)`

  Returns the largest of the values in expression (e.g., `SELECT MAX (elevation) FROM mountains)`.

`MIN(` expression`)`

  Returns the smallest of the values in expression (e.g., `SELECT MIN(level) FROM toxic_waste`).

`STD(` expression`)/STDDEV(`expression)

  Returns the standard deviation of the values in expression (e.g., `SELECT STDDEV(points) FROM data)`.

`SUM(` expression`)`

  Returns the sum of the values in expression (e.g., `SELECT SUM(calories) FROM daily_diet`).

#### Examples

>     # Find all names in the 'people' table where the 'state' field is 'MI'.
>     SELECT name FROM people WHERE state='MI'
>     # Display all of the data in the 'mytable' table.
>     SELECT * FROM mytable

SET |

    SET OPTION SQL_OPTION=value

Defines an option for the current session. Values set by this statement are not in effect anywhere but the current connection, and they disappear at the end of the connection. The following options are current supported:

`CHARACTER SET` charsetname or `DEFAULT`

  Changes the character set used by MySQL. Currently the only other built-in character set is `cp1251_koi8, which` refers to the Russian alphabet. Specifying `DEFAULT` will return to the original character set.

`LAST_INSERT_ID`=number

  Determines the value returned from the `LAST_INSERT_ID()` function.

`SQL_BIG_SELECTS`=0 or 1

  Determines the behavior when a large `SELECT` query is encountered. If set to 1, MySQL will abort the query with an error if the query would probably take too long to compute. MySQL decides that a query will take too long it will have to examine more rows than the value of the `max_join_size` server variable. The default value is 0, which allows all queries.

`SQL_BIG_TABLES`=0 or 1

  Determines the behavior of temporary tables (usually generated when dealing with large data sets). If this value is 1, temporary tables are stored on disk, which is slower than primary memory but can prevent errors on systems with low memory. The default value is 0, which stores temporary tables in RAM.

`SQL_LOG_OFF`=0 or 1

  When set to 1, turns off standard logging for the current session. This does not stop logging to the ISAM log or the update log. You must have `PROCESS LIST` privileges to use this option. The default is 0, which enables regular logging. Chapter 4, "MySQL", describes the various MySQL logging schemes.

`SQL_SELECT_LIMIT`=number

  The maximum number of records returned by a `SELECT` query. A `LIMIT` modifier in a `SELECT` statement overrides this value. The default behavior is to return all records.

`SQL_UPDATE_LOG`=0 or 1

  When set to 0, turns off update logging for the current session. This does not affect standard logging or ISAM logging. You must have `PROCESS LIST` privileges to use this option. The default is 1, which enables regular logging.

`TIMESTAMP`=value or `DEFAULT`

  Determines the time used for the session. This time is logged to the update log and will be used if data is restored from the log. Specifying `DEFAULT` will return to the system time.

#### Example

>     # Turn off logging for the current connection.
>     SET OPTION SQL_LOG_OFF=1

SHOW |

    SHOW DATABASES [LIKE clause]
    SHOW KEYS FROM table [FROM database]
    SHOW INDEX FROM table [FROM database]
    SHOW TABLES [FROM database] [LIKE clause]
    SHOW COLUMNS FROM table [FROM database] [LIKE clause]
    SHOW FIELDS FROM table [FROM database] [LIKE clause]
    SHOW STATUS
    SHOW TABLE STATUS [FROM database] [LIKE clause]
    SHOW VARIABLES [LIKE clause]

Displays various information about the MySQL system. This statement can be used to examine the status or structure of almost any part of MySQL.

#### Examples

>     # Show the available databases
>     SHOW DATABASES
>     # Display information on the indexes on table 'bigdata'
>     SHOW KEYS FROM bigdata
>     # Display information on the indexes on table 'bigdata' in the database 'mydata'
>     SHOW INDEX FROM bigdata FROM mydata
>     # Show the tables available from the database 'mydata' that begin with the
>     # letter 'z'
>     SHOW TABLES FROM mydata LIKE 'z%'
>     # Display information about the columns on the table 'skates'
>     SHOW COLUMNS FROM stakes
>     # Display information about the columns on the table 'people' that end with
>     # '_name'
>     SHOW FIELDS FROM people LIKE '%\_name'
>     # Show server status information.
>     SHOW STATUS
>     # Display server variables
>     SHOW VARIABLES

UNLOCK |

    UNLOCK TABLES

Unlocks all tables that were locked using the `LOCK` statement during the current connection.

#### Example

>     # Unlock all tables
>     UNLOCK TABLES

UPDATE |

    UPDATE table SET column=value, ... [WHERE clause]

Alters data within a table. This statement is used to change actual data within a table without altering the table itself. You may use the name of a column as a value when setting a new value. For example, `UPDATE health SET miles_ran=miles_ran+5` would add five to the current value of the `miles_ran` column. The statement returns the number of rows changed.

You must have `UPDATE` privileges to use this statement.

#### Example

>     # Change the name 'John Deo' to 'John Doe' everywhere in the people table.
>     UPDATE people SET name='John Doe' WHERE name='John Deo'

USE |

    USE database

Selects the default database. The database given in this statement is used as the default database for subsequent queries. Other databases may still be explicitly specified using the `database.table.column` notation.

#### Example

>     # Make db1 the default database.

>     USE db1

Copyright © 2001 O'Reilly & Associates. All rights reserved.